﻿package model.db;

/** 
 * kings
 * * 数据库连接、选择、更新、删除
 * */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import util.KPrintInfo;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBserver {
	
	static private DBserver	_dbserver;
	
	private String			dbHost;
	private String			dbPort;
	private String			dbName;
	private String			userName;
	private String			Psw;
	
	private Connection		dbconn;
	private Statement		dbstate;
	private ResultSet		dbresult;
	
	private ComboPooledDataSource cpds = new ComboPooledDataSource();
	
	public DBserver() {
		
	}
	
	static public DBserver getInstance() {
		if (_dbserver == null) {
			_dbserver = new DBserver();
		}
		
		return _dbserver;
	}
	
	private boolean ObserverDBstate() {
//		try {
//			if (dbstate.isClosed()) {
//				KPrintInfo.println("DBServer---ObserverDBstate--mysql is close--try connection");
//				return dbConnection(dbHost, dbPort, dbName, userName, Psw);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
		return false;
	}
	
	/**
	 * 连接 MySql 数据库
	 * 
	 * @param host
	 * @param port
	 * @param dbaName
	 * @param usName
	 * @param psw
	 * @return bool 值，连接成功返回真，失败返回假
	 */
	public boolean dbConnection(String host, String port, String dbaName, String usName, String psw) {
		String driverName = "com.mysql.jdbc.Driver";
		
		dbHost = host;// 数据库的一些信息
		
		dbPort = port;
		dbName = dbaName;
		String enCoding = "?useUnicode=true&characterEncoding=gb2312"; // 解决 MySql 中文问题,要连续写不能空格
		
		userName = usName;
		
		Psw = psw;
		String url = "jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName + enCoding;
		try {
//			Class.forName(driverName).newInstance();
//			dbconn = DriverManager.getConnection(url, userName, Psw);
			
			cpds.setDriverClass(driverName);
			cpds.setJdbcUrl(url);
			cpds.setUser(userName);
			cpds.setPassword(Psw);
			cpds.setMaxStatements(180);
			cpds.setMaxPoolSize(100);
			
//			从数据库获取新连接失败后重复尝试的次数
			cpds.setAcquireRetryAttempts(30);
			
			cpds.setMaxStatements(0);
			
			cpds.setTestConnectionOnCheckin(true);

			cpds.setAutomaticTestTable("C3P0TestTable");

			dbconn = cpds.getConnection();
			
		} catch (Exception e) {
			print("url = " + url); // 发生错误时，将连接数据库信息打印出来
			print("userName = " + userName);
			print("Psw" + Psw);
			print("Exception: " + e.getMessage());// 得到出错信息
		}
		return createStatement();
		
	}// end boolean dbConnection(…)
	
	private boolean createStatement() {
		if (dbconn != null)// dbconn != null 表示连接数据库成功，由异常保证！？
		{
			KPrintInfo.println("--连接数据库成功--");
			try {
				dbstate = dbconn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			return true;
		} else {
			return false;
		}
	}
	

	/**
	 * 更改数据库，没测试 过
	 * @param url
	 * @param userName
	 * @param Psw
	 * @return
	 */
	public boolean getConnection(String url, String userName, String Psw) {
		try {
			dbconn = DriverManager.getConnection(url, userName, Psw);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return createStatement();
	}
	
	/**
	 * * 对数据库表进行选择操作！ *
	 * 
	 * @param tableName
	 *            数据库表名
	 * @param fieles
	 *            字段名
	 * @param selCondition
	 *            选择条件 * @return 一个含有 map 的 List（列表）
	 */
	public ArrayList dbSelect(String tableName, ArrayList fields, String selCondition) {
		ObserverDBstate();
		
		ArrayList mapInList = new ArrayList();
		String selFields = "";
		for (int i = 0; i < fields.size(); ++i) {
			selFields += fields.get(i) + ", ";
		}
		String selFieldsTem = selFields.substring(0, selFields.length() - 2);// 根据// String	// 的索引提取子串
																			
		try {
			
			String sql = "select " + selFieldsTem + " from " + tableName + selCondition;
			
			try {
				dbresult = dbstate.executeQuery(sql);
			} catch (Exception err) {
				print("Sql = " + sql);
				print("Exception: " + err.getMessage());
			}
			
			while (dbresult.next()) {
				Map selResult = new HashMap();
				
				// selResult.put("message_content", dbresult.getString("message_content"));
				for (int j = 0; j < fields.size(); j++) {
					String keys = (String) fields.get(j);
					String valueString = dbresult.getString(keys);
					selResult.put(keys, valueString);
					// KPrintInfo.println("sex----" + dbresult.getInt("sex"));
					
				}
				mapInList.add(selResult);
			}
			
		} catch (Exception e) {
			print("Exception: " + e.getMessage());
		}
		return mapInList;
	}// end String dbSelect(…)
	
	/**
	 * * 对数据库表中的记录进行删除操作 *
	 * 
	 * @param tableName
	 *            *
	 * @param condition
	 *            *
	 * @return bool 值，表示删除成功或者失败。
	 */
	public boolean dbDelete(String tableName, String condition) {// ——–>>>删除操作
		ObserverDBstate();
		
		boolean delResult = false;
		String sql = "delete from " + tableName + " " + condition;
		try {
			dbstate.executeUpdate(sql); // return int // int delRe = ??
			delResult = true;
		} catch (Exception e) {
			print("sql = " + sql);
			print("Exception: " + e.getMessage());
		}
		if (delResult) {
			return true;
		} else {
			return false;
		}
	}// end dbDelete(…)
	
	/**
	 * * 对数据库表中记录进行更新操作 *
	 * 
	 * @param tabName
	 *            *
	 * @param reCount
	 *            *
	 * @return bool 值，成功返回 true，失败返回 false
	 */
	public boolean dbUpdate(String tabName, HashMap reCount, String upCondition) {
		ObserverDBstate();
		
		boolean updateResult = false;
		String Values = "";
		Iterator keyValues = reCount.entrySet().iterator();
		for (int i = 0; i < reCount.size(); ++i) {
			Map.Entry entry = (Map.Entry) keyValues.next();
			Object key = entry.getKey();
			Object value = entry.getValue();
			Values += key + "=" + "'" + value + "'" + ", ";
		}
		String updateValues = Values.substring(0, Values.length() - 2);
		String sql = "update " + tabName + " set " + updateValues + " " + upCondition;
		
		try {
			dbstate.executeUpdate(sql);
			updateResult = true;
		} catch (Exception err) {
			print("sql = " + sql);
			print("Exception: " + err.getMessage());
		}
		if (updateResult)
			return true;
		else
			return false;
	}// end dbUpdate(…)
	
	/**
	 * * 对数据库表进行插入操作 *
	 * 
	 * @param tabName
	 *            *
	 * @param hm
	 *            *
	 * @return bool 值，成功返回 true，失败返回 false
	 */
	public boolean dbInsert(String tabName, HashMap values) {
		ObserverDBstate();
		
		String sql = "";
		String insertFields = "", temFields = "";
		String insertValues = "", temValues = "";
		boolean insertResult = false;
		Iterator keyValues = values.entrySet().iterator();
		for (int i = 0; i < values.size(); ++i) {
			Map.Entry entry = (Map.Entry) keyValues.next();
			Object key = entry.getKey();
			Object value = entry.getValue();
			temFields += key + ", ";
			temValues += "'" + value + "'" + ", ";
		}
		
		insertFields = temFields.substring(0, temFields.length() - 2);
		insertValues = temValues.substring(0, temValues.length() - 2);
		sql += "insert into " + tabName + " (" + insertFields + ") values" + "(" + insertValues + ")";
		try {
			dbstate.executeUpdate(sql);
			insertResult = true;
		} catch (Exception e) {
			print("Sql = " + sql);
			print("Exception: " + e.getMessage());
		}
		
		if (insertResult)
			return true;
		else
			return false;
	}// end dbInsert(…)
	
	/**
	 * * 断开数据库 *
	 * 
	 * @return bool 值，成功返回 true，失败返回 false
	 */
	public boolean dbClose() {
		boolean closeResult = false;
		try {
			dbconn.close();
			closeResult = true;
		} catch (Exception e) {
			print("Exception: " + e.getMessage());
		}
		if (closeResult)
			return true;
		else
			return false;
	}// end dbClose()
	
	// ///////////////////////////////////////———–>>>类方法
	private void print(String str)// 简化输出
	{
		System.out.println(str);
	}// end print(…)
	
	// end class
}
